﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Linq;
using System.Text;
using DatabaseSchemaReader.DataSchema;
using DatabaseSchemaReader.SqlGen;
using Dataglot;

namespace IrishSettings.Installation
{
    public class InstallIrishSettings
    {
        public static void DropAllTables(string connectionStringName, string prefix)
        {
            ParameterlessCommand com = new ParameterlessCommand(ConfigUtils.DefaultDataFactory());
            
            string sql = "DROP TABLE " +prefix + "_settings_values";
            com.Execute(sql);
            sql = "DROP table " + prefix + "_settings_user";
            com.Execute(sql);
            sql ="DROP table " + prefix + "_settings_schema";
            com.Execute(sql);
            sql= "DROP table " + prefix + "_settings_applications";
            com.Execute(sql);
        }

        public static void CreateAllTables(string connectionStringName, string prefix)
        {
            DatabaseSchema schemaNew = new DatabaseSchema(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString, "System.Data.SqlClient");
            CreateApplicationsTable(schemaNew, prefix);
            CreateSchemaTable(schemaNew, prefix);
            CreateUserTable(schemaNew, prefix);
            CreateValuesTable(schemaNew, prefix);

            DdlGeneratorFactory gen = new DdlGeneratorFactory(SqlType.SqlServer);
            ITablesGenerator tablesGenerator = gen.AllTablesGenerator(schemaNew);
            tablesGenerator.IncludeSchema = false;
            string result2 = tablesGenerator.Write();

            ParameterlessCommand com = new ParameterlessCommand(ConfigUtils.DefaultDataFactory());
            com.Execute(result2);
        }


        private static void CreateApplicationsTable(DatabaseSchema schemaNew, string prefix)
        {

            DatabaseTable dt = schemaNew.AddTable(prefix + "_settings_applications");

            DatabaseColumn dc = new DatabaseColumn();
            dc.Name = "id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = true;
            dc.IsPrimaryKey = true;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 1;
            dc.Nullable = false;
            DatabaseConstraint constraint = new DatabaseConstraint();
            constraint.ConstraintType = ConstraintType.PrimaryKey;
            constraint.Columns.Add("id");
            dt.PrimaryKey = constraint;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "application";
            dc.DbDataType = "nvarchar";
            dc.Length = 50;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 2;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "version";
            dc.DbDataType = "nvarchar";
            dc.Length = 50;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 3;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "is_active_version";
            dc.DbDataType = "bit";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 4;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "culture";
            dc.DbDataType = "nvarchar";
            dc.Length = 10;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 5;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "current_version";
            dc.DbDataType = "nvarchar";
            dc.Length = 10;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 6;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "machine_name";
            dc.DbDataType = "nvarchar";
            dc.Length = 10;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 7;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "domain_name";
            dc.DbDataType = "nvarchar";
            dc.Length = 10;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 8;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "app_settings_key";
            dc.DbDataType = "nvarchar";
            dc.Length = 10;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 9;
            dc.Nullable = true;
            dt.AddColumn(dc);
        } //End table

        private static void CreateSchemaTable(DatabaseSchema schemaNew, string prefix)
        {

            DatabaseTable dt = schemaNew.AddTable(prefix + "_settings_schema");

            DatabaseColumn dc = new DatabaseColumn();
            dc.Name = "id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = true;
            dc.IsPrimaryKey = true;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 1;
            dc.Nullable = false;
            DatabaseConstraint constraint = new DatabaseConstraint();
            constraint.ConstraintType = ConstraintType.PrimaryKey;
            constraint.Columns.Add("id");
            constraint.Name = "PK_settings_schema";
            dt.PrimaryKey = constraint;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "name";
            dc.DbDataType = "nvarchar";
            dc.Length = 300;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 2;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "default_serialized_value";
            dc.DbDataType = "nvarchar";
            dc.Length = 4000;
            //dc.Length = -1;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 3;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "application_id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 4;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "scope";
            dc.DbDataType = "nvarchar";
            dc.Length = 15;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 5;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "application";
            dc.DbDataType = "nvarchar";
            dc.Length = 150;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 6;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "value_type";
            dc.DbDataType = "nvarchar";
            dc.Length = 500;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 7;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "culture";
            dc.DbDataType = "nvarchar";
            dc.Length = 15;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 8;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "is_user";
            dc.DbDataType = "bit";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 9;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "is_anonymous";
            dc.DbDataType = "bit";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 10;
            dc.Nullable = true;
            dt.AddColumn(dc);


        } //End table

        private static void CreateUserTable(DatabaseSchema schemaNew, string prefix)
        {

            DatabaseTable dt = schemaNew.AddTable(prefix + "_settings_user");

            DatabaseColumn dc = new DatabaseColumn();
            dc.Name = "id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = true;
            dc.IsPrimaryKey = true;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 1;
            dc.Nullable = false;
            DatabaseConstraint constraint = new DatabaseConstraint();
            constraint.ConstraintType = ConstraintType.PrimaryKey;
            constraint.Columns.Add("id");
            constraint.Name = "PK_Settings_User";
            dt.PrimaryKey = constraint;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "schema_id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 2;
            dc.Nullable = false;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "user_name";
            dc.DbDataType = "nvarchar";
            dc.Length = 250;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 3;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "is_anonymous";
            dc.DbDataType = "bit";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 4;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "name";
            dc.DbDataType = "nvarchar";
            dc.Length = 300;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 5;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "serialized_value";
            dc.DbDataType = "nvarchar";
            dc.Length = -1;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 6;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "version";
            dc.DbDataType = "nvarchar";
            dc.Length = 50;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 7;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "application";
            dc.DbDataType = "nvarchar";
            dc.Length = 150;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 8;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "value_type";
            dc.DbDataType = "nvarchar";
            dc.Length = 500;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 9;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "culture";
            dc.DbDataType = "nvarchar";
            dc.Length = 15;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 10;
            dc.Nullable = true;
            dt.AddColumn(dc);

        } //End table

        private static void CreateValuesTable(DatabaseSchema schemaNew, string prefix)
        {

            DatabaseTable dt = schemaNew.AddTable(prefix + "_settings_values");

            DatabaseColumn dc = new DatabaseColumn();
            dc.Name = "id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = true;
            dc.IsPrimaryKey = true;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 1;
            dc.Nullable = false;
            DatabaseConstraint constraint = new DatabaseConstraint();
            constraint.ConstraintType = ConstraintType.PrimaryKey;
            constraint.Columns.Add("id");
            constraint.Name = "PK_settings_values";
            dt.PrimaryKey = constraint;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "application_id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 2;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "schema_id";
            dc.DbDataType = "smallint";
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 3;
            dc.Nullable = false;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "version";
            dc.DbDataType = "nvarchar";
            dc.Length = 50;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 4;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "serialized_value";
            dc.DbDataType = "nvarchar";
            dc.Length = 4000;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 5;
            dc.Nullable = true;
            dt.AddColumn(dc);

            dc = new DatabaseColumn();
            dc.Name = "value_type";
            dc.DbDataType = "nvarchar";
            dc.Length = 4000;
            dc.IsIdentity = false;
            dc.IsPrimaryKey = false;
            dc.IsUniqueKey = false;
            dc.IsIndexed = false;
            dc.Ordinal = 6;
            dc.Nullable = true;
            dt.AddColumn(dc);

        } //End table
    }
}
